package studio.raptor.databus.graph.it;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.commons.lang3.StringUtils;
import org.h2.Driver;
import org.h2.tools.DeleteDbFiles;

/**
 * @author jack
 * @since 1.0
 */
public class H2DatabaseHelper {

  private String userName;
  private String dbUrl;
  private String password;
  private String path;
  private String db;

  public H2DatabaseHelper(String path, String db, String userName, String password, int dbMode)
      throws SQLException {
    Driver.load();
    this.path = path;
    this.db = db;
    if (dbMode == 1) {
      this.dbUrl = String.format("jdbc:h2:mem:%s;FILE_LOCK=SOCKET", db);
    } else if (dbMode == 2) {
      this.dbUrl = String.format("jdbc:h2:%s/%s;FILE_LOCK=SOCKET", path, db);
    }
    this.password = password;
    this.userName = userName;
  }

  @SuppressWarnings("unused")
  private boolean execute(String sql) throws SQLException {
    try (
        Connection connection = tryToGetConnection();
        Statement statement = connection.createStatement()
    ) {
      return statement.execute(sql);
    }
  }

  public void prepareH2Database() throws SQLException, IOException {
    DeleteDbFiles.execute(path, db, false);
    String[] filePaths = new String[]{"/sql/all_table.sql", "/sql/csip_c2u_events_new.sql",
        "/sql/csip_c2u_paras_new.sql"};
    BufferedReader[] bufferedReaders = new BufferedReader[3];
    try (
        Connection connection = tryToGetConnection();
        Statement statement = connection.createStatement()
    ) {
      for (int i = 0; i < filePaths.length; i++) {
        bufferedReaders[i] = new BufferedReader(
            new InputStreamReader(H2DatabaseHelper.class.getResourceAsStream(filePaths[i]),
                "utf-8"));
      }

      for (BufferedReader bufferedReader : bufferedReaders) {
        String sqlLine;
        while (null != (sqlLine = bufferedReader.readLine())) {
          if (!StringUtils.isEmpty(sqlLine) && !sqlLine.trim().startsWith("--")) {
            statement.execute(sqlLine);
          }
        }
      }

    } finally {
      for (BufferedReader bufferedReader : bufferedReaders) {
        bufferedReader.close();
      }
    }
  }

  public Connection tryToGetConnection() throws SQLException {
    return DriverManager.getConnection(this.dbUrl, this.userName, this.password);
  }

}
